﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using UFC.Common;
using System.Data;
using UFC.DBUtility;
using System.Xml;
using Newtonsoft.Json;
using System.IO;
using System.Text.RegularExpressions;
using System.Collections; 

namespace SendIndustrialNetwork
{
    public class RBResourceStatusStepPlan
    {
        private const string m_TempCommiType = "SendRBResourceSatus";//推送热表状态采集系统
        private static readonly string m_FilePath = ConfigurationManager.AppSettings["acceptFilePath"];//文件接收的位置
        private static readonly string m_BackFilePath = ConfigurationManager.AppSettings["backFilePath"];//文件备份的位置
        private string m_file = "";

        public void Excute()
        {
            LogHelp.SetConfig();
            try
            {
              SendData();//数据发送          
            }
            catch (Exception ex)
            {
                LogHelp.WriteError("推送给热表设备状态系统数据：" + ex.Message, ex);
                WriteLogAndSendExchange(new FileInfo(m_file), "RBPLANformsend", "false", ex.Message, "", "MES推送热表系统任务的返回信息", "mes_app", "MES推送热表结果_处理日志");
            }


            try
            {
                DealReciveData(); //处理异步返回的消息     
            }
            catch (Exception ex)
            {
                LogHelp.WriteError("接收热表设备状态系统数据：" + ex.Message, ex);
                WriteLogAndSendExchange(new FileInfo(m_file), "RBPLANformreturn", "false", ex.Message, "", "MES处理热表DNC结果的返回信息", "mes_app", "MES接收热表DNC结果_处理日志");
            }
          
        }

         /// <summary>
        /// 数据发送处理
        /// </summary>
        public void SendData() {
            var dt = GetNeedDealData();


            foreach (DataRow mainRow in dt.Rows) {
                var checkItemDt = OracleHelper.Query(string.Format(@"select st.stepcheckpointid,st.checkno,st.checkpointnote,st.drawingrequire,st.checkpointnotedis_b,st.drawingrequiredis_b
,st.basevalue,st.floorfloat,st.ceilfloat from stepcheckpoint st where st.workflowstepid='{0}' and st.stepinfoid='{1}'", 
mainRow["workflowstepid"].ToString(), mainRow["stepinfoid"].ToString())).Tables[0];

                var requestXml = CreateSendTaskXML(mainRow,checkItemDt);

                #region 临时测试用，无误后取消
                string fileName ="MES给热表设备采集系统任务"+DateTime.Now.ToString("yyyyMMddHHmmssffffff")+ ".xml"; // + ".xml";
                //requestXml.Save(m_BackFilePath+"\\" + fileName);
                #endregion

                string msg = LYIndustryFileExchangeHelper.FileUpload("11013", fileName, requestXml.OuterXml, "");//文件推送给数据交换系统
                string code = "";
                if (!string.IsNullOrWhiteSpace(msg))
                {
                    try
                    {
                        var reMsgObj = JsonConvert.DeserializeObject<Dictionary<string, string>>(msg);
                        code = reMsgObj["code"];
                        LYIndustryFileExchangeHelper.SendSysLogByUDP(code, mainRow["applyFullname"].ToString(), fileName, "xml", requestXml.OuterXml.Length.ToString(), "");
                    }catch(Exception ex){
                        WriteLogAndSendExchange(new FileInfo(m_file), "RBPLANformreturn", "false", msg, "", "MES推送热表返回信息Token失效", "mes_app", "MES接收热表DNC结果_处理日志");
                        CacheHelper.SetToken("token_11013", string.Empty);
                        continue;
                    }
                }
                //推送完毕，更新业务数据，写入MES数据库日志

                OracleDAL.InserOtherSystemRecord(new Dictionary<string, string>() { 
                 {"ParentId",mainRow ["DispatchID"].ToString ()},{"RecordType",m_TempCommiType},
                        {"ContainerID",mainRow ["containerid"].ToString ()},{"ContainerName",mainRow ["containername"].ToString ()},{"IsSuccess",code=="200"?"0":"1"},
                        {"ProductName",mainRow ["productname"].ToString ()},{"WorkflowStepName",mainRow ["workflowstepname"].ToString ()},
                        {"SendXml",requestXml.OuterXml},{"ResXml",msg}
            });

            }
        }

        /// <summary>
        /// 接收后数据处理
        /// </summary>
        void DealReciveData()
        {
            var dirs = new DirectoryInfo(m_FilePath);
            foreach (FileInfo fi in dirs.GetFiles())
            {
                if (fi.Extension.Replace(".", "").ToLower() != "xml")
                    continue;
                m_file = fi.FullName;
                FileStream fileStream = new FileStream(fi.FullName, FileMode.Open, FileAccess.Read);
                StreamReader streamReader = new StreamReader(fileStream);
                var str= streamReader.ReadToEnd();
                fileStream.Close();
                //解析并判断业务
                XmlDocument myxml = new XmlDocument();
                // myxml.Load(fi.FullName);
                myxml.LoadXml(str);
                //获取业务方法验证
                if (myxml.SelectSingleNode("root/header/BusinessFun") == null)
                {
                    continue;
                }
                var businessFun = myxml.SelectSingleNode("root/header/BusinessFun").InnerText;
                try
                {
                    switch (businessFun)
                    {
                        case "ReceiveRBReportFromDNC":
                            ReceiveRBReportFromDNC(myxml, fi);
                            break;
                        case "SendRBDispatchToDNC_ResMsg"://处理MES给热表的尺寸信息后，热表返回接收成功/失败的消息 
                            SendRBDispatchToDNC_ResMsg(myxml, fi);
                            break;
                        default:
                            break;

                    }
                }
                catch (Exception ex)
                {
                    LogHelp.WriteOrcale("MES接收热表DNC结果_处理异常" + "@^" + ex.Message + "@^" + fi.Name + " " + myxml.InnerXml + "@^" + Convert.ToString(Guid.NewGuid()) + "@^" + "");
                    LYIndustryFileExchangeHelper.MoveBackFile(fi, "");
                }

            }
        }
        /// <summary>
        /// 接收热表设备采集系统的报工数据
        /// </summary>
        /// <param name="myxml"></param>
        /// <param name="fi"></param>
        void ReceiveRBReportFromDNC(XmlDocument myxml, FileInfo fi)
        {
            //业务实际处理
            string dispatchID = myxml.SelectSingleNode("root/document/DispatchID").InnerText;
            LogHelp.WriteOrcale("MES接收热表DNC结果_处理日志" + "@^" + "开始处理:" + fi.Name + "@^" + myxml.InnerXml + "@^" + Convert.ToString(Guid.NewGuid()) + "@^" + dispatchID);

            DataTable dispatchDt = OracleHelper.Query(string.Format(@"select csd.* from containerstepdispatch2 csd where csd.containerstepdispatchid='{0}' and isuse=0", dispatchID)).Tables[0];

            if (dispatchDt.Rows.Count == 0) {
                WriteLogAndSendExchange(fi, "RBPLANformreturn", "false", "MES中未找到此任务,对应字段：DispatchID", dispatchID, "MES处理热表DNC结果的返回信息", "mes_app", "MES接收热表DNC结果_处理日志");
                return;    
            }
            var resourceName = myxml.SelectSingleNode("root/document/ReportResName").InnerText;
            var resourceid = OracleHelper.GetSingle("select rd.resourceid from resourcedef rd where rd.resourcename='" + resourceName + "'");
            if (resourceid == null)
            {
                WriteLogAndSendExchange(fi, "RBPLANformreturn", "false", "MES中未找到此设备,对应字段：ReportResName", dispatchID, "MES处理热表DNC结果的返回信息", "mes_app", "MES接收热表DNC结果_处理日志");
                return;                    
            }
            var reportEmpName = myxml.SelectSingleNode("root/document/ReportEmpName").InnerText;
            var reportEmpDt = OracleHelper.Query(string.Format(@"select sv.workcenterid,te.teamid,e.employeeid from employee e
left join sessionvalues sv on sv.sessionvaluesid=e.sessionvaluesid
left join teamemployee te on te.employeeid=e.employeeid
where e.employeename='{0}'", reportEmpName)).Tables[0];
            if (reportEmpDt.Rows.Count == 0) {
                WriteLogAndSendExchange(fi, "RBPLANformreturn", "false", "MES中未找到此人员,对应字段：ReportEmpName", dispatchID, "MES处理热表DNC结果的返回信息", "mes_app", "MES接收热表DNC结果_处理日志");
                return;    
            }

            var para = new Dictionary<string, string>();
            var reportID = Guid.NewGuid().ToString();
            para.Add("DispatchID", dispatchID);
            para.Add("CheckType", "2");
            para.Add("ContainerID", dispatchDt.Rows[0]["ContainerID"].ToString());
            para.Add("WorkflowStepID", dispatchDt.Rows[0]["WorkflowStepID"].ToString());
            para.Add("StepInfoID", dispatchDt.Rows[0]["StepInfoID"].ToString());
            para.Add("CreateDate", myxml.SelectSingleNode("root/document/ReportDate").InnerText);
            para.Add("FactoryID", dispatchDt.Rows[0]["FactoryID"].ToString());
            para.Add("Isuse", "0");
            para.Add("Qty", myxml.SelectSingleNode("root/document/ReportQty").InnerText);
            para.Add("ResourceID", resourceid.ToString());
            para.Add("TeamID", reportEmpDt.Rows[0]["TeamID"].ToString());
            para.Add("WorkcenterID", reportEmpDt.Rows[0]["WorkcenterID"].ToString());
            para.Add("WorkerID", reportEmpDt.Rows[0]["employeeid"].ToString());
            para.Add("Lossqty", "0");
            para.Add("ContainerStepWorkReportID", reportID); 
            //数据验证
            if (IsDateTime(para["CreateDate"]) == false) {
                WriteLogAndSendExchange(fi, "RBPLANformreturn", "false", "时间格式错误,对应字段：ReportDate", dispatchID, "MES处理热表DNC结果的返回信息", "mes_app", "MES接收热表DNC结果_处理日志");
                return;   
            }
            var temp=0;
            if (int.TryParse(para["Qty"], out temp) == false)
            {
                WriteLogAndSendExchange(fi, "RBPLANformreturn", "false", "数字格式错误,对应字段：ReportQty", dispatchID, "MES处理热表DNC结果的返回信息", "mes_app", "MES接收热表DNC结果_处理日志");
                return;   
            }

            var sqlList = new ArrayList();
            //是否存在验证
            if (OracleHelper.Query(string.Format (@"select * from containerstepworkreport2 where containerstepdispatchid='{0}' and isuse=0 ", dispatchID)).Tables[0].Rows.Count  > 0)
            {
                WriteLogAndSendExchange(fi, "RBPLANformreturn", "false", "此任务MES中已有报工", dispatchID, "MES处理热表DNC结果的返回信息", "mes_app", "MES接收热表DNC结果_处理日志");
                return; 
            }
            string reportSql = @"insert into containerstepworkreport2 csw (csw.containerstepdispatchid,csw.checktype,csw.containerid,csw.workflowstepid,csw.createdate,csw.factoryid,
csw.isuse,csw.qty,csw.resourceid,csw.teamid,csw.workcenterid,csw.workerid,csw.lossqty,csw.containerstepworkreportid,StepInfoID) 
values ('{0}','{1}','{2}','{3}',to_date('{4}','yyyy-mm-dd hh24:mi:ss'),'{5}','{6}',{7},'{8}','{9}','{10}','{11}','{12}','{13}','{14}')";//报工信息
            sqlList.Add(string.Format(reportSql, para["DispatchID"], para["CheckType"], para["ContainerID"], para["WorkflowStepID"], para["CreateDate"], para["FactoryID"], para["Isuse"],
                para["Qty"], para["ResourceID"], para["TeamID"], para["WorkcenterID"], para["WorkerID"], para["Lossqty"], para["ContainerStepWorkReportID"], para["StepInfoID"]));

            //子序号获取
            DataTable childConDt = OracleHelper.Query(string.Format (@"select c.containername,c.containerid from container c
where c.status=1 and c.parentcontainerid='{0}' ", dispatchDt.Rows[0]["ContainerID"].ToString())).Tables[0];
            if (childConDt.Rows.Count > 0) {
                foreach (DataRow childCon in childConDt.Rows) {
                    sqlList.Add(string.Format("INSERT INTO CONTAINERSTEPWORKREPORTCHILD2(CONTAINERSTEPWORKREPORTID,CHILDCONTAINERID,SELFCHECKLOSSQTY) VALUES('{0}','{1}',null)",
                        para["ContainerStepWorkReportID"], childCon["containerid"].ToString()));
                }
            }

            //处理检测项
            var checkItems = myxml.SelectSingleNode("root/document/CheckItems");

            DataTable checkItemDt = OracleHelper.Query(string.Format(@"select st.* from stepcheckpoint st where st.workflowstepid='{0}' and st.stepinfoid='{1}'",
                dispatchDt.Rows[0]["WorkflowStepID"].ToString(), dispatchDt.Rows[0]["stepinfoid"].ToString())).Tables[0];

            foreach (XmlNode checkItem in checkItems.ChildNodes)
            {
                string checkItemId = checkItem.SelectSingleNode("CheckItemID").InnerText;
                if (checkItemDt.Select("stepcheckpointid='"+checkItemId+"'").Length == 0)
                {
                    WriteLogAndSendExchange(fi, "RBPLANformreturn", "false", "MES中未找到此检测项,对应字段：CheckItemID", dispatchID, "MES处理热表DNC结果的返回信息", "mes_app", "MES接收热表DNC结果_处理日志");
                    return;   
                }
                string result= checkItem.SelectSingleNode("CheckResult").InnerText;
                GetSaveCheckResultSql(dispatchDt.Rows[0], checkItemId, childConDt, reportID, para["WorkerID"], para["CreateDate"], result, ref sqlList);

            }

            OracleHelper.ExecuteSqlTran(sqlList);//工步报工完毕

            //工步级完工检查,开始检查工序级报工
            sqlList.Clear();
            
            //工步派工记录完工状态检查
            if (OracleHelper.GetSingle(string.Format (@"select 1 from containerstepworkreport2 csw 
where csw.isuse=0 and csw.containerstepdispatchid='{0}'
having sum(csw.qty)>=(select sum(csd.qty) from containerstepdispatch2 csd 
where csd.containerstepdispatchid='{0}' and csd.isuse=0)", dispatchID)) != null)
            {
                sqlList.Add(string.Format("update containerstepdispatch2 set ISCOMPLETE=1 where containerstepdispatchid='{0}'", dispatchID));
            }

            //批次基本信息
            DataTable containerBase = OracleHelper.Query(string.Format(@"select cci.qty wipqty from containercurrentinfo cci where cci.containerid='{0}'", para["ContainerID"])).Tables[0];
            //查询此批次，此工序下的所有工步报工信息
            DataTable allStepInfoReport = OracleHelper.Query(string.Format(@"select si.workflowstepid,si.stepinfoid,max(csw.qty) stepinfoqty from stepinfo si
left join containerstepworkreport2 csw on csw.containerid='{0}' and csw.workflowstepid=si.workflowstepid and csw.stepinfoid=si.stepinfoid
and csw.isuse=0
where si.workflowstepid='{1}'
group by si.workflowstepid,si.stepinfoid", para["ContainerID"], para["WorkflowStepID"])).Tables[0];

            if (allStepInfoReport.Select("stepinfoqty<" + containerBase.Rows[0]["wipqty"].ToString() + " or stepinfoqty is null ").Length == 0&&
                OracleHelper.GetSingle(string.Format("select 1 from containerstepworkreport where containerid='{0}' and workflowstepid='{1}' and isuse=0 ", 
                para["ContainerID"], para["WorkflowStepID"])) == null)//工序级不存在报工,工步级报工完工
            {

                var faDispatcID=OracleHelper.GetSingle(string.Format ("select containerstepdispatchid from containerstepdispatch where containerid='{0}' and workflowstepid='{1}'",
                    para["ContainerID"], para["WorkflowStepID"])).ToString();

                if(faDispatcID!=null){//工序级有派工，才自动报工
                    var faReportID=Guid.NewGuid().ToString();
                    sqlList.Add(string.Format(@"insert into containerstepworkreport csw (csw.containerstepdispatchid,csw.checktype,csw.containerid,csw.workflowstepid,csw.createdate,csw.factoryid,
csw.isuse,csw.qty,csw.resourceid,csw.teamid,csw.workcenterid,csw.workerid,csw.lossqty,csw.containerstepworkreportid) 
values ('{0}','{1}','{2}','{3}',to_date('{4}','yyyy-mm-dd hh24:mi:ss'),'{5}','{6}',{7},'{8}','{9}','{10}','{11}','{12}','{13}')", faDispatcID.ToString(), "2", para["ContainerID"], para["WorkflowStepID"], para["CreateDate"], para["FactoryID"], para["Isuse"],
                para["Qty"], para["ResourceID"], para["TeamID"], para["WorkcenterID"], para["WorkerID"], para["Lossqty"], faReportID));
                    foreach (DataRow childCon in childConDt.Rows)
                    {
                        sqlList.Add(string.Format("INSERT INTO CONTAINERSTEPWORKREPORTCHILD2(CONTAINERSTEPWORKREPORTID,CHILDCONTAINERID,SELFCHECKLOSSQTY) VALUES('{0}','{1}',null)",
                            faReportID, childCon["containerid"].ToString()));
                    }

                    //工序级完工标记位处理
                    sqlList.Add(string.Format("update containerstepdispatch2 set ISCOMPLETE=1 where containerstepdispatchid='{0}'", faDispatcID.ToString()));

                }
                OracleHelper.ExecuteSqlTran(sqlList);//工序报工完毕
                


            }



            //处理成功，返回消息
            WriteLogAndSendExchange(fi, "RBPLANformreturn", "true", "MES接收热表DNC结果成功", dispatchID, "MES处理热表DNC结果的返回信息", "mes_app", "MES接收热表DNC结果_处理日志");
        }

        /// <summary>
        /// 检测项结果保存的sql
        /// </summary>
        /// <param name="dispatchItem"></param>
        /// <param name="childConDt"></param>
        /// <param name="employeeid"></param>
        /// <param name="createDate"></param>
        /// <returns></returns>
        void GetSaveCheckResultSql(DataRow dispatchItem, string checkItemId, DataTable childConDt, string reportID, string employeeid, string createDate, string result, ref ArrayList sqlList)
        {
            DataTable existDt = OracleHelper.Query(string.Format(@"select * from stepcheckpointresult sr where sr.containerid='{0}'  and sr.stepcheckpointid='{1}'",
                dispatchItem["containerid"].ToString(), checkItemId)).Tables[0];

            string updateSql = @"update STEPCHECKPOINTRESULT sr set sr.workerid='{0}',sr.workcheckpointresultnote='{1}',sr.workcheckpointresultnotedis='{2}',
sr.createdate=to_date('{3}','yyyy/mm/dd hh24:mi:ss'),sr.tempdealfail=0,lastchangedate=sysdate where sr.stepcheckpointresultid='{4}'";

            string insertSql = @"INSERT INTO STEPCHECKPOINTRESULT (lastchangedate,WORKERID,WORKCHECKPOINTRESULTNOTE,WORKCHECKPOINTRESULTNOTEDIS,CONTAINERID,ChildContainerID,CREATEDATE,FACTORYID,STEPCHECKPOINTID,
STEPCHECKPOINTRESULTID,ContainerStepWorkReportID) VALUES (sysdate,'{0}','{1}','{2}','{3}','{4}',to_date('{5}','yyyy/mm/dd hh24:mi:ss'),'{6}','{7}','{8}','{9}')";

            if (childConDt.Rows.Count > 0)
            {
                foreach (DataRow childRow in childConDt.Rows)
                {
                    var childCheckItem = existDt.Select("ChildContainerID='" + childRow["containerid"].ToString() + "'");
                    if (childCheckItem.Length > 0)
                    {
                        sqlList.Add(string.Format(updateSql, employeeid, result, result, createDate, childCheckItem[0]["stepcheckpointresultid"].ToString()));
                    }
                    else
                    {
                        sqlList.Add(string.Format(insertSql, employeeid, result, result, dispatchItem["containerid"].ToString(), childRow["containerid"].ToString(), createDate,
                            dispatchItem["FACTORYID"].ToString(), checkItemId, Guid.NewGuid().ToString(), reportID));
                    }
                }
            }
            else
            {
                if (existDt.Rows.Count > 0)
                {
                    sqlList.Add(string.Format(updateSql, employeeid, result, result, createDate, existDt.Rows[0]["stepcheckpointresultid"].ToString()));
                }
                else
                {
                    sqlList.Add(string.Format(insertSql, employeeid, result, result, dispatchItem["containerid"].ToString(), "", createDate,
                             dispatchItem["FACTORYID"].ToString(), checkItemId, Guid.NewGuid().ToString(), reportID));
                }
            }
        }

        /// <summary>
        /// 三坐标返回信息，发送给三坐标任务
        /// </summary>
        /// <param name="myxml"></param>
        /// <param name="fi"></param>
        void SendRBDispatchToDNC_ResMsg(XmlDocument myxml, FileInfo fi)
        {
            string id = myxml.SelectSingleNode("root/document/DispatchID").InnerText;
            var isSuccess = myxml.SelectSingleNode("root/document/isSuccess").InnerText == "true" ? "0" : "1";
            string msg = myxml.SelectSingleNode("root/document/msg").InnerText;

            OracleHelper.ExecuteSql(string.Format("writetoothersystem wt set wt.issuccess='{0}',wt.resxml=wt.resxml||'{1}' where wt.parentid='{2}' ",
                isSuccess, " " + myxml.OuterXml.Replace("\n", "").Replace("\r", "").Trim(' '), id));
            LYIndustryFileExchangeHelper.MoveBackFile(fi, "");
        }

        /// <summary>
        /// 获取待处理的主数据
        /// </summary>
        /// <returns></returns>
        DataTable GetNeedDealData() {
            string strSql = @"
--热表设备数据
select fac.containerid facontainerid,cci.containerid,cci.containername,cci.workflowstepname,cci.mfgordername,nvl(cci.workflowroute,cci.productname) productname,cci.productdescription,csd.containerstepdispatchid DispatchID,
cci.realcontainerno RbContainer,fac.realcontainerno ContainerNo,cci.workflowname RbWorkflow,cci.qty,cci.familylistnames ProductFamilys,
cci.specno,s.opdesc,si.stepno StepInfoNo,si.description StepInfoDesc,fp.Materialtrademark faMaterialtrademark,
fp.MaterialSpecification faMaterialSpecification,rd.resourcename DispatchResName,rd.description DispatchResDesc,wm_concat(distinct e.fullname) ToDispEmpFullName,
wm_concat(distinct e.employeename) ToDispEmpName ,csd.workflowstepid,csd.stepinfoid,ae.fullname applyFullname,'' Notes,cci.containertype
 from containercurrentinfo cci
inner join containerstepdispatch2 csd on csd.containerid=cci.containerid and csd.workflowstepid=cci.workflowstepid and csd.isuse=0
left join spec s on s.specid=cci.specid
left join stepinfo si on si.stepinfoid=csd.stepinfoid
left join containersubcontainer cc on cc.subcontainerid=cci.containerid
left join containercurrentinfo fac on fac.containerid=cc.containerid--父卡
left join product fp on fp.productid=fac.productid--父件号

left join resourcedef rd on rd.resourceid=csd.resourceid
left join containerstepdispatchdetail2 csdd on csdd.containerstepdispatchid=csd.containerstepdispatchid--明细
left join employee e on e.employeeid=nvl(csd.workerid,csdd.workerid)
left join employee ae on ae.employeeid=csd.employeeid
where cci.factoryname like '热表%' AND csd.isuse=0  --and csd.containerid = '00041080000a3dc5'  AND si.stepinfoid = '4800af8000005361' 
and csd.createdate>=to_date('2023-05-24 17:00:00','yyyy-MM-dd hh24:mi:ss')" + "\r\n";

            strSql += string.Format(" and not exists (select 1 from writetoothersystem wt where wt.parentid=csd.containerstepdispatchid and wt.recordtype='{0}')", m_TempCommiType);
            strSql += " AND NOT EXISTS (SELECT 1 FROM containerstepworkreport2 csw WHERE csw.containerstepdispatchid=csd.containerstepdispatchid AND csw.isuse=0)";

            strSql += @" group by fac.containerid,cci.mfgordername,nvl(cci.workflowroute,cci.productname) ,cci.productdescription,csd.containerstepdispatchid,
cci.realcontainerno ,cci.realcontainerno ,cci.workflowname ,cci.qty,cci.familylistnames ,fac.realcontainerno,
cci.specno,s.opdesc,si.stepno ,si.description ,fp.Materialtrademark ,ae.fullname,cci.containerid,cci.containername,cci.workflowstepname,
fp.MaterialSpecification ,rd.resourcename ,rd.description,csd.workflowstepid,csd.stepinfoid,cci.containertype";

            DataTable dt=OracleHelper.Query(strSql).Tables[0];

            dt.Columns.AddRange(new DataColumn[]{
                new DataColumn("DLDH"),new DataColumn("DLPH"), new DataColumn("DLGG"),new DataColumn("PXZYDH"),new DataColumn("PXZYGX"),new DataColumn("RLH")
            });

            foreach (DataRow dr in dt.Rows) {
                var faAtrr = OracleHelper.Query(string.Format(@"select * from userattribute ut where ut.parentid='{0}'", dr["facontainerid"].ToString())).Tables[0];

                if (faAtrr.Rows.Count > 0) {
                    dr["DLDH"] = faAtrr.Select("userattributename='代料单号'").FirstOrDefault() == null ? "" : faAtrr.Select("userattributename='代料单号'").FirstOrDefault()["attributevalue"].ToString();
                    dr["DLPH"] = faAtrr.Select("userattributename='代料牌号'").FirstOrDefault() == null ? "" : faAtrr.Select("userattributename='代料牌号'").FirstOrDefault()["attributevalue"].ToString();
                    dr["DLGG"] = faAtrr.Select("userattributename='代料规格'").FirstOrDefault() == null ? "" : faAtrr.Select("userattributename='代料规格'").FirstOrDefault()["attributevalue"].ToString();
                    dr["PXZYDH"] = faAtrr.Select("userattributename='平行作业单号'").FirstOrDefault() == null ? "" : faAtrr.Select("userattributename='平行作业单号'").FirstOrDefault()["attributevalue"].ToString();
                    dr["PXZYGX"] = faAtrr.Select("userattributename='平行作业工序'").FirstOrDefault() == null ? "" : faAtrr.Select("userattributename='平行作业工序'").FirstOrDefault()["attributevalue"].ToString();
                    dr["RLH"] = faAtrr.Select("userattributename='熔炉号母'").FirstOrDefault() == null ? "" : faAtrr.Select("userattributename='熔炉号母'").FirstOrDefault()["attributevalue"].ToString();
                }
            }

            dt.AcceptChanges();

            DataTable dt2 = dt.DefaultView.ToTable(true, "DispatchID", "MfgOrderName", "ProductName", "productdescription", "RbContainer", "RbWorkflow", "Qty", "ProductFamilys"
               , "DLDH", "DLPH", "DLGG", "Notes", "SpecNo", "Opdesc", "StepInfoNo", "StepInfoDesc", "PXZYDH", "PXZYGX", "faMaterialtrademark", "faMaterialSpecification", "RLH"
               , "DispatchResDesc", "DispatchResName", "ToDispEmpFullName", "ToDispEmpName", "workflowstepid", "WorkflowStepName", "stepinfoid", "containertype", "containername", "containerid", "applyFullname");
            dt2.Columns.Add("ContainerNo");
            //DataTable dt1=dt.Copy();
            foreach(DataRow row in dt2.Rows)
            {
                var ContainerNo=string.Empty;
                if(row["containertype"].ToString().Equals("3")){
                    DataRow[] frow=dt.Select("containerid='"+row["containerid"]+"'");
                    if(frow.Length>0){
                        for(var i=0;i<frow.Length;i++){
                            ContainerNo+=frow[i]["ContainerNo"]+",";
                        }
                    }
                }
                if(!string.IsNullOrWhiteSpace(ContainerNo)){
                    row["ContainerNo"]=ContainerNo.TrimEnd(',');
                }
            }

            return dt2;
        }

        /// <summary>
        /// 推送给热表设备状态任务
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="checkItemDt"></param>
        /// <returns></returns>
        XmlDocument CreateSendTaskXML(DataRow dr, DataTable checkItemDt) {
            XmlDocument doc = new XmlDocument();
            doc.AppendChild(doc.CreateXmlDeclaration("1.0", "utf-8", ""));
            XmlNode root = doc.CreateElement("root");
            root.AppendChild(doc.CreateElement("header"));

            root.LastChild.AppendChild(doc.CreateElement("BusinessFun"));
            root.LastChild.LastChild.InnerText = "RBPLANformsend";//三坐标的业务方法,待定

            XmlNode document = doc.CreateElement("document");//业务主体

            document.AppendChild(doc.CreateElement("DispatchID"));
            document.LastChild.InnerText = dr["DispatchID"].ToString();

            document.AppendChild(doc.CreateElement("MfgOrderName"));
            document.LastChild.InnerText = dr["MfgOrderName"].ToString();

            document.AppendChild(doc.CreateElement("ProductName"));
            document.LastChild.InnerText = dr["ProductName"].ToString();

            document.AppendChild(doc.CreateElement("ProductDesc"));
            document.LastChild.InnerText = dr["productdescription"].ToString();

            document.AppendChild(doc.CreateElement("ContainerNo"));
            document.LastChild.InnerText = dr["ContainerNo"].ToString();

            document.AppendChild(doc.CreateElement("RbContainer"));
            document.LastChild.InnerText = dr["RbContainer"].ToString();

            document.AppendChild(doc.CreateElement("RbWorkflow"));
            document.LastChild.InnerText = dr["RbWorkflow"].ToString();

            document.AppendChild(doc.CreateElement("Qty"));
            document.LastChild.InnerText = dr["Qty"].ToString();

            document.AppendChild(doc.CreateElement("ProductFamilys"));
            document.LastChild.InnerText = dr["ProductFamilys"].ToString();

            document.AppendChild(doc.CreateElement("DLDH"));
            document.LastChild.InnerText = dr["DLDH"].ToString();

            document.AppendChild(doc.CreateElement("DLPH"));
            document.LastChild.InnerText = dr["DLPH"].ToString();

            document.AppendChild(doc.CreateElement("DLGG"));
            document.LastChild.InnerText = dr["DLGG"].ToString();

            document.AppendChild(doc.CreateElement("Notes"));
            document.LastChild.InnerText = dr["Notes"].ToString();

            document.AppendChild(doc.CreateElement("SpecNo"));
            document.LastChild.InnerText = dr["SpecNo"].ToString();

            document.AppendChild(doc.CreateElement("Opdesc"));
            document.LastChild.InnerText = dr["Opdesc"].ToString();

            document.AppendChild(doc.CreateElement("StepInfoNo"));
            document.LastChild.InnerText = dr["StepInfoNo"].ToString();

            document.AppendChild(doc.CreateElement("StepInfoDesc"));
            document.LastChild.InnerText = dr["StepInfoDesc"].ToString();

            document.AppendChild(doc.CreateElement("PXZYDH"));
            document.LastChild.InnerText = dr["PXZYDH"].ToString();

            document.AppendChild(doc.CreateElement("PXZYGX"));
            document.LastChild.InnerText = dr["PXZYGX"].ToString();

            document.AppendChild(doc.CreateElement("MaterialTrademark"));
            document.LastChild.InnerText = dr["faMaterialtrademark"].ToString();

            document.AppendChild(doc.CreateElement("MaterialSpecification"));
            document.LastChild.InnerText = dr["faMaterialSpecification"].ToString();

            document.AppendChild(doc.CreateElement("RLH"));
            document.LastChild.InnerText = dr["RLH"].ToString();

            document.AppendChild(doc.CreateElement("DispatchResDesc"));
            document.LastChild.InnerText = dr["DispatchResDesc"].ToString();

            document.AppendChild(doc.CreateElement("DispatchResName"));
            document.LastChild.InnerText = dr["DispatchResName"].ToString();

            document.AppendChild(doc.CreateElement("ToDispEmpFullName"));
            document.LastChild.InnerText = dr["ToDispEmpFullName"].ToString();

            document.AppendChild(doc.CreateElement("ToDispEmpName"));
            document.LastChild.InnerText = dr["ToDispEmpName"].ToString();

            XmlNode checkItems = doc.CreateElement("CheckItems");//检测项

                foreach (DataRow checkItem in checkItemDt.Rows) {
                    var checkItemNode = doc.CreateElement("CheckItem");

                    checkItemNode.AppendChild(doc.CreateElement("CheckItemID"));
                    checkItemNode.LastChild.InnerText = checkItem["stepcheckpointid"].ToString();

                    checkItemNode.AppendChild(doc.CreateElement("CheckNo"));
                    checkItemNode.LastChild.InnerText = checkItem["CheckNo"].ToString();

                    checkItemNode.AppendChild(doc.CreateElement("checkpointnote"));
                    checkItemNode.LastChild.AppendChild(doc.CreateCDataSection(checkItem["checkpointnotedis_b"].ToString()));

                    checkItemNode.AppendChild(doc.CreateElement("drawingrequire"));
                    checkItemNode.LastChild.AppendChild(doc.CreateCDataSection(checkItem["drawingrequiredis_b"].ToString()));

                    checkItems.AppendChild(checkItemNode);
                }
            


            document.AppendChild(checkItems);
            root.AppendChild(document);
            doc.AppendChild(root);

            return doc;
        }

        /// <summary>
        /// 日志写入，包含文本日志，数据库日志，给日志系统的日志，给对方业务系统的日志
        /// </summary>
        /// <param name="fi">发生日志的文件</param>
        /// <param name="businessFun">给对方业务系统的日志，对方的业务方法</param>
        /// <param name="isSuccess">true/false</param>
        /// <param name="reqMsg">日志描述</param>
        /// <param name="mainId">业务数据主id</param>
        /// <param name="sendFileName">给日志系统的日志，给对方业务系统的日志的文件名</param>
        /// <param name="operater">操作者</param>
        /// <param name="logMark">文本日志，数据库日志的标记</param>
        void WriteLogAndSendExchange(FileInfo fi, string businessFun, string isSuccess, string reqMsg, string mainId, string sendFileName, string operater, string logMark)
        {
            string returnMsg = ReturnMessage(businessFun, isSuccess, reqMsg, mainId);
            LogHelp.WriteLog(logMark + returnMsg);
            sendFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + "MesToDnc.xml";
            string msg = LYIndustryFileExchangeHelper.FileUpload("11013", sendFileName, returnMsg, "");//文件推送给数据交换系统
            string code = "";
            if (!string.IsNullOrWhiteSpace(msg))
            {
                var reMsgObj = JsonConvert.DeserializeObject<Dictionary<string, string>>(msg);
                code = reMsgObj["code"];
                LYIndustryFileExchangeHelper.SendSysLogByUDP(code, operater, sendFileName, "xml", returnMsg.Length.ToString(), "");
            }
            LYIndustryFileExchangeHelper.MoveBackFile(fi, "");
            LogHelp.WriteOrcale(logMark + "@^" + returnMsg + "@^" + fi.Name + "@^" + Convert.ToString(Guid.NewGuid()) + "@^" + mainId);
        }

        /// <summary>
        /// 返回消息生成
        /// </summary>
        /// <param name="toAppFun"></param>
        /// <param name="isSuccess"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        string ReturnMessage(string toAppFun, string isSuccess, string msg, string id)
        {
            string str = string.Format(@"<?xml version=""1.0"" encoding=""UTF-8""?><root><header><BusinessFun>{0}</BusinessFun></header><document><DispatchID>{3}</DispatchID><isSuccess>{1}</isSuccess><msg>{2}</msg></document></root>",
                toAppFun, isSuccess, msg, id);

            return str;
        }

        /// <summary>
        /// 日期验证
        /// </summary>
        /// <param name="StrSource"></param>
        /// <returns></returns>
        public bool IsDateTime(string StrSource)
        {
            return Regex.IsMatch(StrSource, @"^(((((1[6-9]|[2-9]\d)\d{2})-(0?[13578]|1[02])-(0?[1-9]|[12]\d|3[01]))|(((1[6-9]|[2-9]\d)\d{2})-(0?[13456789]|1[012])-(0?[1-9]|[12]\d|30))|(((1[6-9]|[2-9]\d)\d{2})-0?2-(0?[1-9]|1\d|2[0-8]))|(((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))-0?2-29-)) (20|21|22|23|[0-1]?\d):[0-5]?\d:[0-5]?\d)$");
        }
    }
}
